/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50141
Source Host           : localhost:3306
Source Database       : sport

Target Server Type    : MYSQL
Target Server Version : 50141
File Encoding         : 65001

Date: 2014-02-14 14:59:06
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `clubs`
-- ----------------------------
DROP TABLE IF EXISTS `clubs`;
CREATE TABLE `clubs` (
`clubid`  int(11) NOT NULL AUTO_INCREMENT ,
`name`  varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`name_se`  varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`nickname`  varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`country`  char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'en' ,
`logo`  varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`slug`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`short`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`description`  text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`tag`  varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`created`  datetime NOT NULL ,
`modified`  datetime NULL DEFAULT NULL ,
`logid`  int(11) NOT NULL DEFAULT 0 ,
`status`  tinyint(4) NOT NULL DEFAULT 0 ,
`co_post`  int(11) NOT NULL DEFAULT 0 ,
`co_fan`  int(11) NOT NULL ,
`co_like`  int(11) NOT NULL DEFAULT 0 ,
`co_follow`  int(11) NOT NULL DEFAULT 0 ,
`co_sponsor`  smallint(6) NOT NULL DEFAULT 0 ,
PRIMARY KEY (`clubid`),
FULLTEXT INDEX `FT_name_nickname_tag_short` (`name_se`, `nickname`, `short`, `tag`) 
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1

;

-- ----------------------------
-- Records of clubs
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Procedure structure for `clubs_add`
-- ----------------------------
DROP PROCEDURE IF EXISTS `clubs_add`;

CREATE PROCEDURE `clubs_add`(ploginid int,pname varchar(150),pnickname varchar(150),pcountry char(2),plogo varchar(150),pslug varchar(255),pshort varchar(255),pdescription text,ptag varchar(100))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	DECLARE rClubId INT DEFAULT 0;
	DECLARE rNameSe VARCHAR(150) DEFAULT NULL;
	IF rErrCode=0 AND _admin_check_permission(1<<1,ploginid,'club')=0 AND ploginid > 0 THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode = 0 THEN
		SET rNameSe = _com_trim_str(pname);		
		INSERT clubs(name, name_se, nickname, country, logo,
								slug, short, description, tag, created, 
								modified, status, logid)
		VALUE(pname, rNameSe, pnickname, pcountry, plogo,
					pslug, pshort, pdescription, ptag, NOW(),
					NOW(), 1, ploginid);
		SET rClubId = LAST_INSERT_ID();	
	END IF;
	SELECT rErrCode AS errCode, rClubId AS clubid;
END;

-- ----------------------------
-- Procedure structure for `clubs_delete`
-- ----------------------------
DROP PROCEDURE IF EXISTS `clubs_delete`;

CREATE PROCEDURE `clubs_delete`(ploginid int(11),pclubid int(11))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	DECLARE rLogo VARCHAR(150) DEFAULT NULL;
	DECLARE rType TINYINT(4) DEFAULT 0;
	DECLARE rPerm TINYINT(4) DEFAULT 0;
	SET rType = _admin_role(ploginid);
	SET rPerm = _admin_check_permission(1<<3,ploginid,'category');
	IF rErrCode=0 AND (rPerm=0 OR rType < 5) THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode = 0 THEN
		SELECT logo INTO rLogo FROM clubs WHERE clubid=pclubid;
		DELETE FROM clubs WHERE clubid=pclubid;
	END IF;
	SELECT rErrCode AS errCode;
END;

-- ----------------------------
-- Procedure structure for `clubs_get`
-- ----------------------------
DROP PROCEDURE IF EXISTS `clubs_get`;

CREATE PROCEDURE `clubs_get`(pclubid int(11))
    READS SQL DATA
BEGIN	
		SELECT *
		FROM clubs
		WHERE clubid=pclubid;
END;

-- ----------------------------
-- Procedure structure for `clubs_getall_a`
-- ----------------------------
DROP PROCEDURE IF EXISTS `clubs_getall_a`;

CREATE PROCEDURE `clubs_getall_a`(ploginid int,pstatus tinyint,poption tinyint(4)/**0:all*/,pkeyword varchar(50),pstart date,pend date,poffset int,plimit smallint(6))
    READS SQL DATA
BEGIN
	#call: 1,-1,0,'tu+khoa',null,null,0,10
	DECLARE rFoundRows INT DEFAULT 0;
	DECLARE rType TINYINT DEFAULT 0;
	DECLARE rStatus TINYINT DEFAULT 0;
	CREATE TEMPORARY TABLE IF NOT EXISTS tmpclubs(clubid bigint) TYPE = MEMORY;	
	# permission view
	SET rType = _admin_role(ploginid);
	IF rType < 3 THEN SET rStatus = 3;
	ELSE SET rStatus = rType + 1;	
	END IF;
	SET @sqlStm = CONCAT('
												INSERT tmpclubs(clubid)
												SELECT SQL_CALC_FOUND_ROWS
														clubid
												FROM clubs
												WHERE clubid IS NOT NULL',	
													IF(pkeyword !='',
															IF(poption=0,
																 CONCAT(' AND MATCH(name_se,short,tag) AGAINST(',QUOTE(pkeyword),' IN BOOLEAN MODE)'),
																 ELT(poption,
																		CONCAT(' AND name_se LIKE ''%',pkeyword,'%'''),
																		CONCAT(' AND short LIKE ''%',pkeyword,'%'''),
																		CONCAT(' AND tag LIKE ''%',pkeyword,'%''')
																 )
															),
															''
													),
													IF(pstatus > -1, 
														CONCAT(' AND ABS(status)=',pstatus),
														CONCAT(' AND ABS(status)<',rStatus)
													),													
													IF(pstart <> '0000-00-00' AND pend <> '0000-00-00', CONCAT(' AND LEFT(created,10) BETWEEN ''',pstart,''' AND ''',pend,''''),''),
													IF(pstart <> '0000-00-00' AND pend = '0000-00-00', CONCAT(' AND LEFT(created,10)=''',pstart,''''),''),
													IF(pstart = '0000-00-00' AND pend <> '0000-00-00', CONCAT(' AND LEFT(created,10)=''',pend,''''),''),
												' ORDER BY clubid DESC
												LIMIT ',poffset,',',plimit
											);

	PREPARE stmt FROM @sqlStm;
	EXECUTE stmt;

	SET rFoundRows = FOUND_ROWS();
	
	SELECT SQL_SMALL_RESULT rFoundRows AS foundRows, B.*
	FROM tmpclubs A JOIN clubs B ON A.clubid=B.clubid;

	DROP TEMPORARY TABLE IF EXISTS tmpclubs;	
END;

-- ----------------------------
-- Procedure structure for `clubs_update`
-- ----------------------------
DROP PROCEDURE IF EXISTS `clubs_update`;

CREATE PROCEDURE `clubs_update`(ploginid int,pclubid int(11),pname varchar(150),pnickname varchar(150),pcountry char(2),plogo varchar(150),pslug varchar(255),pshort varchar(255),pdescription text,ptag varchar(100))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	DECLARE rClubId INT DEFAULT 0;
	DECLARE rNameSe VARCHAR(150) DEFAULT NULL;
	DECLARE rLogo VARCHAR(150) DEFAULT NULL;

	IF rErrCode=0 AND _admin_check_permission(1<<2,ploginid,'club')=0 AND ploginid > 0 THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode = 0 THEN
		SET rLogo = (SELECT logo FROM clubs WHERE clubid=pclubid);
		SET rNameSe = _com_trim_str(pname);	
		UPDATE clubs 
		SET name=pname,
				name_se=rNameSe,
				nickname=pnickname,
				country=pcountry,
				logo=IF(plogo != '', plogo, logo),
				slug=pslug,
				short=pshort,
				description=pdescription,
				tag=ptag,
				modified=NOW(),
				logid=plogid
		WHERE clubid=pclubid;
	END IF;
	SELECT rErrCode AS errCode, rLogo AS logo;
END;

-- ----------------------------
-- Procedure structure for `clubs_update_status`
-- ----------------------------
DROP PROCEDURE IF EXISTS `clubs_update_status`;

CREATE PROCEDURE `clubs_update_status`(ploginid int(11),pclubid int(11),pstatus tinyint(4),pperm tinyint(4))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	IF rErrCode=0 AND _admin_check_permission(pperm,ploginid,'club')=0 AND ploginid > 0 THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode=0 THEN		
		UPDATE clubs SET `status`=pstatus WHERE clubid=pclubid;	
	END IF;
	SELECT rErrCode AS errCode;
END;

-- ----------------------------
-- Function structure for `_admin_role`
-- ----------------------------
DROP FUNCTION IF EXISTS `_admin_role`;

CREATE FUNCTION `_admin_role`(ploginid int) RETURNS int(11)
BEGIN
	DECLARE rType TINYINT(4) DEFAULT 0;
	SET rType = (SELECT type FROM admins WHERE adminid=ploginid) ;
	RETURN rType;
END;

-- ----------------------------
-- Auto increment value for `clubs`
-- ----------------------------
ALTER TABLE `clubs` AUTO_INCREMENT=1;
